﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using ScriptsGenerator.Manager;
using Microsoft.Office.Interop.Word;
using ScriptDumper.Manager;
using ScriptsGenerator.Helper;

namespace ScriptsGenerator.Forms
{
    public partial class PatchForm : Form
    {
        public PatchForm()
        {
            InitializeComponent();
        }

        private Microsoft.Office.Interop.Word.ApplicationClass _wordApp;
        public ApplicationClass WordApp
        {
            get
            {
                if (_wordApp == null)
                    _wordApp = new ApplicationClass();
                return _wordApp;
            }
            set
            {
                _wordApp = value;
            }
        }

        private object missing = System.Reflection.Missing.Value;

        private Microsoft.Office.Interop.Word.Document _wordDocument = null;
        private Microsoft.Office.Interop.Word.Document WordDocument
        {
            get
            {
                return _wordDocument;
            }
            set
            {
                _wordDocument = value;
            }
        }

        private DbVersion SelectedVersion
        {
            get
            {
                if (AppInfo.CurrentContext.Versions.ContainsKey(cbbVersion.Text))
                {
                    return AppInfo.CurrentContext.Versions[cbbVersion.Text];
                }

                return new DbVersion();
            }
        }

        private void btnPicWordFilename_Click(object sender, EventArgs e)
        {
            if (ofdOpenDocDialog.ShowDialog(this) == System.Windows.Forms.DialogResult.Cancel)
            {
                // 使用者沒有選檔案
                return;
            }

            tbDocName.Text = ofdOpenDocDialog.FileName; // 選擇的完整路徑

            if (string.IsNullOrEmpty(tbExportingFilename.Text) &&
                !string.IsNullOrEmpty(tbDocName.Text))
                tbExportingFilename.Text = tbDocName.Text.Substring(0, tbDocName.Text.LastIndexOf('.')) + ".sql";

            cbbVersion.DataSource = GetVersions();
        }

        private void btnPickExportingFilename_Click(object sender, EventArgs e)
        {
            if (sfdExportingFilename.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
                return;

            tbExportingFilename.Text = sfdExportingFilename.FileName; 
        }

        private void tbDocName_TextChanged(object sender, EventArgs e)
        {

        }

        private void tbDocName_Leave(object sender, EventArgs e)
        {
            cbbVersion.DataSource = GetVersions();
        }

        private string[] GetVersions()
        {
            List<string> versions = new List<string>();

            if (File.Exists(tbDocName.Text))
            {
                try
                {
                    object fileName = tbDocName.Text;
                    object readOnly = false;
                    object isVisible = false;
                    object missing = System.Reflection.Missing.Value;
                    WordApp.Visible = false;

                    _wordDocument = WordApp.Documents.Open(ref fileName, ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible, ref missing, ref missing, ref missing, ref missing);

                    for (int i = 1; i <= _wordDocument.Tables.Count; i++)
                    {
                        Microsoft.Office.Interop.Word.Table table = _wordDocument.Tables[i];

                        try
                        {
                            AppInfo.CurrentContext.Versions.Clear();

                            string text = table.Range.Cells[1].Range.Text.TrimEnd('\a', '\r');
                            // 抬頭為Document Amendment 或文件變更履歷
                            if (text.Contains("DOCUMENT") || 
                                text.Contains("AMENDMENT") ||
                                text.Replace(" ", "").Contains("文件變更履歷"))
                            {
                                string versionNO = string.Empty;

                                for (int j = 3; j <= table.Rows.Count; j++)
                                {
                                    Row row = table.Rows[j];

                                    if (!string.IsNullOrEmpty(row.Range.Cells[1].Range.Text.TrimEnd('\a', '\r')))
                                        versionNO = row.Range.Cells[1].Range.Text.TrimEnd('\a', '\r');

                                    // 版序	生效日期	變更說明	Table	變更	PK	Field
                                    string publishedDate = row.Range.Cells[2].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    string description = row.Range.Cells[3].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    string action = row.Range.Cells[5].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    string tables = row.Range.Cells[4].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    string pkey = row.Range.Cells[6].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    string field = row.Range.Cells[7].Range.Text.TrimEnd('\a', '\r').Trim(' ');
                                    
                                    DbVersion version = null;

                                    if (AppInfo.CurrentContext.Versions.ContainsKey(versionNO) ||
                                        string.IsNullOrEmpty(versionNO))
                                    {
                                        version = AppInfo.CurrentContext.Versions[versionNO];
                                    }
                                    else
                                    {
                                        version = new DbVersion();
                                        version.VersionID = versionNO;
                                        version.PubishedDate = publishedDate;
                                        AppInfo.CurrentContext.Versions.Add(version.VersionID, version);
                                    }

                                    string[] tbls = tables.Split(new char[] { '\a', '\r', ',' }, StringSplitOptions.RemoveEmptyEntries);

                                    foreach (string key in tbls)
                                    {
                                        Amendment amendment = new Amendment();
                                        amendment.Description = description;
                                        amendment.Action = action;
                                        amendment.Field = field;
                                        amendment.Table = key.Trim(' ');

                                        amendment.PKeySequence = 0;
                                        int pk = 0;
                                        if (int.TryParse(pkey, out pk))
                                        {
                                            amendment.PKeySequence = pk;
                                        }

                                        version.Amendments.Add(amendment);
                                    }
                                }

                                break;
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                finally
                {
                    _wordDocument.Close(ref missing, ref missing, ref missing);
                    WordApp.Quit(ref missing, ref missing, ref missing);
                    WordApp = null;
                }               
            }

            foreach (string key in AppInfo.CurrentContext.Versions.Keys)
            {
                versions.Add(key);
            }

            return versions.ToArray();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            tbMessage.Text = string.Empty;

            object fileName = tbDocName.Text;
            object readOnly = false;
            object isVisible = false;
            object missing = System.Reflection.Missing.Value;
            WordApp.Visible = false;

            WordDocument = WordApp.Documents.Open(ref fileName, ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible, ref missing, ref missing, ref missing, ref missing);

            AppInfo.CurrentContext.Tables = DocumentHelper.GetTableInfo(WordDocument);

            WordDocument.Close(ref missing, ref missing, ref missing);
            WordApp.Quit(ref missing, ref missing, ref missing);
            WordApp = null;

            if (!string.IsNullOrEmpty(tbExportingFilename.Text))
            {
                try
                {
                    if (string.IsNullOrEmpty(sfdExportingFilename.FileName))
                    {
                        sfdExportingFilename.FileName = tbExportingFilename.Text;
                    }

                    using (FileStream fs = (FileStream)sfdExportingFilename.OpenFile())
                    {
                        foreach (Amendment amendment in SelectedVersion.Amendments)
                        {
                            try
                            {
                                if (AppInfo.CurrentContext.Tables.ContainsKey(amendment.Table))
                                {
                                    DbTable table = AppInfo.CurrentContext.Tables[amendment.Table];

                                    string outputText = SqlScriptManager.GetInstance().GetPatchedSqlScript(table, amendment);

                                    Byte[] text = new UTF8Encoding(true).GetBytes(outputText);
                                    fs.Write(text, 0, text.Length);
                                }
                                else
                                {
                                    tbMessage.Text += string.Format("Error: {0}.{1} {2}", amendment.Table, amendment.Field, Environment.NewLine);
                                }
                            }
                            catch (Exception ex)
                            {
                                tbMessage.Text += string.Format("Error: {0}.{1} {2}{3}", amendment.Table, amendment.Field, ex.Message, Environment.NewLine);
                            }
                        }

                        fs.Close();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            MessageBox.Show("The SQL Scripts are generated.");
        }

    }
}
